Un-Rolling a Parent-Child Hierarchy in Code

By Cameron Aycock

Often, a developer needs to handle a parent-child relationship hierarchy in their code from some datasource (table, flat file, spreadsheet, or code data structure). This article attempts to address how one can in effect "unroll" this hierarchy in code. My previous attempts at doing such were very complex and prone to errors. Then it finally came to me while working on this exact problem for a client. This is one possible and simple solution for obtaing all of the children of a supplied entity. This example uses the BDE and a table called COMPANY in our database. You can easily modify this for your own tables. Note that the power lies in the TStringList.

In our example we will use a company. This company can have a ParentCompany. Here is an example table "COMPANY" with this relationship:

CompanyName    | e_mail                | ParentCompany
=======================================================
ABC Co         | yadda@abc.org         |
FlashCo        | yadda@flashco.org     | ABC Co
SPAM Network   | spamspamspam@mp.com   | FlashCo
Alabama Pride  | bubba@bamapride.com   | FlashCo
Chitlins Inc.  | gutz@chitlinz.net     | FlashCo

As you can see 3 companies are a part of FlashCo:
 SPAM Network,
 Alabama Pride, and
 Chitlins Inc.

FlashCO only has one parent -- ABC Co

ABC Co is the top level company as it has NULL for its parent.

It is not to difficult in obtaining the parent(s) of a company. It is however fairly difficult to get all of the children of a particular company because a company can have multiple children, directly or indirectly related to it. For instance, ABC Co has the following children:

FlashCo
SPAM Network
Alabama Pride
Chitlins Inc.

Although these children are not the DIRECT decendants of ABC Co, they are still subsidiaries.

For ALL of the children for a parent company, I had to resort to unrolling it in code. There may be a better way to do this with SQL, however I do not know of a standardized way, besides, sometimes this structure presents itself in a code data structure.

The key to unrolling the children lies in the all-powerful TStringList object, and its ability ignore duplicates. The following code snippet is the heart of what I used to find all of the children of a particular company provided;

procedure LoadChildrenCompanies(var slChildrenList: TStringList;
sCompanyName: string);
var
  i: integer;
  qryCompanies: TQuery;
begin
  slChildrenList.Clear();
  slChildrenList.Duplicates := dupIgnore;

  qryCompanies := TQuery.Create();
  try
    qryCompanies.DatabaseName := dbSomeDatabase.DatabaseName;
    qryCompanies.SQL.Text := 'SELECT DISTINCT COMPANYNAME FROM COMPANIES WHERE PARENTCOMPANY = ' +
      #39  + sCompanyName + #39;
    qryCompanies.Open();
    while not qyCompanies.EOF do begin
      slChildrenList.Add(qryCompanies.FieldByName('COMPANYNAME').AsString);
      qryCompanies.Next;
    end;
    qryCompanies.Close();

    i := 0;
    while i < slChildrenList.Count do begin
      qryCompanies.SQL.Text := 'SELECT DISTINCT COMPANYNAME FROM WHERE PARENTCOMPANY = ' +
        #39 + slChildrenList.Strings[i] + #39;
      qryCompanies.Open();
      while not qryCompanies.EOF do begin
 
slChildrenList.Add(qryCompanies.FieldByName('COMPANYNAME').AsString);
        qryCompanies.Next();
      end;
      qryCompanies.Close();
      Inc(i);
    end;

  finally
    if Assigned(qryCompanies) then begin
      qryCompanies.Close();
      qryCompanies.Free();
    end;
  end;
end;
 

When this function returns it will have filled the string list you provided with all of the children for the Company specified. Isn't the StringList wonderful?

Please, if you have any improvements, comments, or suggestions send em on.

(xagon7 at yahoo.com) -- replace " at " with @.

Have Fun!
Cameron Aycock